package Dao.Impl;

import Dao.ArticleDAO;
import Dto.ArticleDTO;
import Dto.UserDto;
import entity.Article;
import entity.ArticleAttachment;
import entity.KnowledgeBase;
import entity.User;
import utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author Su
 * @Date 2025/4/28 23:15
 * @Version 1.0 （版本号）
 */
public class ArticleDAOImpl implements ArticleDAO {
    private Connection connection;

    public ArticleDAOImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public int createArticle(Article article) throws SQLException {
        String sql = "INSERT INTO articles (knowledge_base_id, title, content, author_id) VALUES (?, ?, ?, ?)";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setInt(1, article.getKnowledgeBaseId());
            stmt.setString(2, article.getTitle());
            stmt.setString(3, article.getContent());
            stmt.setInt(4, article.getAuthorId());

            int affectedRows = stmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("创建文章失败，没有行受影响");
            }

            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getInt(1);
                } else {
                    throw new SQLException("创建文章失败，无法获取ID");
                }
            }
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Override
    public Article getArticleById(int id) throws SQLException {
        String sql = "SELECT * FROM articles WHERE id = ?";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, id);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapRowToArticle(rs);
                }
                return null;
            }
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Override
    public List<Article> getArticlesByKnowledgeBase(int knowledgeBaseId) throws SQLException {
        String sql = "SELECT * FROM articles WHERE knowledge_base_id = ? ORDER BY created_at DESC";
        List<Article> articles = new ArrayList<>();
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, knowledgeBaseId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    articles.add(mapRowToArticle(rs));
                }
            }
        } finally {
            DBUtil.closeConnection(connection);
        }
        return articles;
    }

    @Override
    public boolean updateArticle(Article article) throws SQLException {
        String sql = "UPDATE articles SET title = ?, content = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setString(1, article.getTitle());
            stmt.setString(2, article.getContent());
            stmt.setInt(3, article.getId());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Override
    public boolean deleteArticle(int id) throws SQLException {
        String sql = "DELETE FROM articles WHERE id = ?";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, id);
            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    @Override
    public boolean exists(int id) throws SQLException {
        String sql = "SELECT 1 FROM articles WHERE id = ?";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, id);

            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        } finally {
            DBUtil.closeConnection(connection);
        }
    }

    private Article mapRowToArticle(ResultSet rs) throws SQLException {
        Article article = new Article();
        article.setId(rs.getInt("id"));
        article.setKnowledgeBaseId(rs.getInt("knowledge_base_id"));
        article.setTitle(rs.getString("title"));
        article.setContent(rs.getString("content"));
        article.setAuthorId(rs.getInt("author_id"));
        article.setCreatedAt(rs.getDate("created_at"));
        article.setUpdatedAt(rs.getTimestamp("updated_at") != null
                ? rs.getDate("updated_at")
                : null);
        return article;
    }

    @Override
    public List<ArticleAttachment> getAttachmentsByArticle(int articleId) throws SQLException {
        String sql = "SELECT id, file_name, file_path, file_type, file_size, uploader_id, uploaded_at " +
                "FROM article_attachments WHERE article_id = ?";
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, articleId);

            List<ArticleAttachment> attachments = new ArrayList<>();
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    ArticleAttachment attachment = new ArticleAttachment();
                    attachment.setId(rs.getInt("id"));
                    attachment.setArticleId(articleId);
                    attachment.setFileName(rs.getString("file_name"));
                    attachment.setFilePath(rs.getString("file_path"));
                    attachment.setFileType(rs.getString("file_type"));
                    attachment.setFileSize(rs.getLong("file_size"));
                    attachment.setUploaderId(rs.getInt("uploader_id"));
                    attachment.setUploadedAt(rs.getDate("uploaded_at"));
                    attachments.add(attachment);
                }
            } finally {
                DBUtil.closeConnection(connection);
            }
            return attachments;
        }
    }

    @Override
    public List<ArticleDTO> getAllArticles(int currentUserId) throws SQLException {
        String sql =  "SELECT a.id, a.title, a.content, a.created_at, " +
                "u.user_id as author_id, u.username as author_name, " +
                "kb.id as kb_id, kb.name as kb_name, kb.description, kb.access_level," +
                "(SELECT COUNT(*) FROM article_likes al WHERE al.article_id = a.id AND al.status = 1) as like_count, " +
                "(SELECT COUNT(*) FROM article_comments ac WHERE ac.article_id = a.id) as comment_count " +
                "FROM articles a " +
                "JOIN user u ON a.author_id = u.user_id " +
                "JOIN knowledge_base kb ON a.knowledge_base_id = kb.id " +
                "WHERE kb.access_level = 'PUBLIC' OR kb.creator_id = ? " +
                "ORDER BY a.created_at DESC";

        List<ArticleDTO> articles = new ArrayList<>();
        ResultSet rs = null;
        connection = DBUtil.getConnection();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setInt(1, currentUserId);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ArticleDTO article = new ArticleDTO();
                article.setId(rs.getInt("id"));
                article.setTitle(rs.getString("title"));
                article.setContent(rs.getString("content"));
                article.setCreatedAt(rs.getDate("created_at"));

                UserDto author = new UserDto();
                author.setUserId(rs.getInt("author_id"));
                author.setUsername(rs.getString("author_name"));
                article.setAuthor(author);

                KnowledgeBase kb = new KnowledgeBase();
                kb.setId(rs.getInt("kb_id"));
                kb.setName(rs.getString("kb_name"));
                kb.setDescription(rs.getString("description"));
                kb.setCreatedAt(rs.getDate("created_at"));
                String accessLevelStr = rs.getString("access_level");
                KnowledgeBase.AccessLevel accessLevel = KnowledgeBase.AccessLevel.valueOf(accessLevelStr);
                kb.setAccessLevel(accessLevel);
                article.setKnowledgeBase(kb);

                article.setLikeCount(rs.getInt("like_count"));
                article.setCommentCount(rs.getInt("comment_count"));

                articles.add(article);
            }
        } finally {
            DBUtil.closeConnection(connection);
        }
        return articles;
    }
}